﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using LOTSConversion;
using System.Windows;
namespace Convert.DBconverter
{
    class SuppliersConverter
    {
        private static Dictionary<int, SupplierItemCountMap> LotsToCPIAMSupplierDictionary;
        private static Dictionary<int, Tuple<int, string>> LotsReallocatedSupplierDictionary;
        private static List<CPIAMSupplier> CPIAMSuppliers;
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;

        public static Dictionary<int, Tuple<int, string>> ConvertSuppliers(string lotsConnectionString, string cpiamConnectionString, List<SupplierItemCountMap> supplierItemCountMap, List<CPIAMSupplier> CPIAMSuppliersList)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            CPIAMSuppliers = CPIAMSuppliersList;
            LotsToCPIAMSupplierDictionary = new Dictionary<int, SupplierItemCountMap>();
            LotsReallocatedSupplierDictionary = new Dictionary<int, Tuple<int, string>>();  //new Supplier id and suppler desc
            DataTable SuppliersDT = DataTables.CreateSuppliersDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                foreach (SupplierItemCountMap supplierMap in supplierItemCountMap)
                {
                    LotsToCPIAMSupplierDictionary[supplierMap.LotsSupplierID] = supplierMap;
                }
                dbConn.Open();
                OleDbCommand command = dbConn.CreateCommand();
                command.CommandText = string.Format(@"SELECT SupplierID, SupplierName, Address, City, PhoneNumber, FaxNumber FROM Supplier ORDER BY SupplierName");
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {

                    Supplier supplier = new Supplier();
                    int i = 0;
                    supplier.SupplierID = reader.GetInt32(i);
                    supplier.SupplierName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    supplier.Address = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    supplier.city = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    supplier.PhoneNumber = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    supplier.FaxNumber = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    if (supplier.SupplierName != null)
                        SaveSuppliers(supplier, SuppliersDT);

                    if (SuppliersDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                    {
                        SaveAllDataTables(CpiamConnectionString, SuppliersDT);
                    }
                }
                SaveAllDataTables(CpiamConnectionString, SuppliersDT);
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert Suppliers ", ex);
                MessageBox.Show("Error converting Suppliers: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
            return LotsReallocatedSupplierDictionary;
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable SuppliersDT)
        {
            if (SuppliersDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(SuppliersDT, "Suppliers", ConnectionString);
        }

        public static void SaveSuppliers(Supplier LotsSupplier, DataTable SuppliersDT, bool useCurrentSupplierID = false)
        {
            //if this supplier has been mapped to CPIAM one then do not add it
            if (LotsToCPIAMSupplierDictionary.ContainsKey(LotsSupplier.SupplierID))
            {

                //SupplierMap.Add(LotspSupplier.supplierid.Value, LotsToCPIAMSupplierDictionary[LotspSupplier.supplierid.Value].CPIAMSupplier.SupplierNo);
                LotsReallocatedSupplierDictionary[LotsSupplier.SupplierID] = new Tuple<int, string>(LotsToCPIAMSupplierDictionary[LotsSupplier.SupplierID].CPIAMSupplier.SupplierID, LotsToCPIAMSupplierDictionary[LotsSupplier.SupplierID].CPIAMSupplier.SupplierName);
            }
            else
            {
                //Supplier details in Lots file?
                Guid guidSupplierID = Guid.NewGuid();
                int intSupplierID = LotsSupplier.SupplierID;
                int oldSupplierID = intSupplierID;
                //if the suplierID already exists in CPIAM then assign the next available supplier no
                //if (intSupplierID <= 1000 || CPIAMSuppliers.Where(s => s.SupplierID == intSupplierID).FirstOrDefault() != null || SupplierMap.ContainsKey(intSupplierID))
                //{
                //    //re assign
                //    intSupplierID = GetNextSupplierNumber();
                //}

                //just always assign a new supplier ID
                if (!useCurrentSupplierID)
                    intSupplierID = GetNextSupplierNumber();
                string strSupplierName = "Not Found.";
                string strAddress = "N/A";
                string strSuburb = "N/A";
                strSupplierName = LotsSupplier.SupplierName == null ? "Warning: Supplier Name Not Found." : LotsSupplier.SupplierName.ToUpper();
                strAddress = LotsSupplier.Address == null ? "N/A" : LotsSupplier.Address;
                strSuburb = LotsSupplier.city == null ? "N/A" : LotsSupplier.city;
                Suppliers suppliers = new Suppliers(intSupplierID, strSupplierName, strAddress, strSuburb, "N/A", "0000", LotsSupplier.PhoneNumber, LotsSupplier.FaxNumber, "0000", "N/A", "N/A");
                Suppliers.SaveSuppliersToDataTable(suppliers, SuppliersDT);

                LotsReallocatedSupplierDictionary[oldSupplierID] = new Tuple<int, string>(intSupplierID, strSupplierName);
            }
        }

        private static int? nextSupplierNo = null;
        public static int GetNextSupplierNumber()
        {
            if (!nextSupplierNo.HasValue)
            {
                nextSupplierNo = CPIAMSuppliers.Select(s => s.SupplierID).Max();
                if (nextSupplierNo <= 1000)
                    nextSupplierNo = 1000;
            }
            nextSupplierNo = nextSupplierNo.Value + 1;

            return nextSupplierNo.Value;
        }
    }
}

